﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Meeting.DBUtility;
using Meeting.DBUtility;
using Newtonsoft.Json;
using System.Data.SqlClient;

namespace Meeting.BLL
{
    /// <summary>
    /// 模块作者：吴龙波
    /// 模块日期：2010-12-31
    /// 模块功能：部门表department 增删查改
    /// </summary>
    public class Department
    {
        public string errorString = "";

        //获取部门表数据
        public DataSet GetDept(string filter)
        {
            string sql = "Select * From dbo.Department Where 1=1 ";
            if (!string.IsNullOrEmpty(filter)) sql += " And " + filter;
            try
            {
                return DbHelperSQL.Query(sql);
            }
            catch (Exception Ex)
            {
                errorString = Ex.Message;
                return null;
            }
        }

        /// <summary>
        /// 获取部门树的Json格式
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public string GetDeptJson(string filter)
        {
            try
            {
                string sql = "Select Id  As id,ParentId As pId,Name As name,Comment,Sort  From dbo.Department Where 1=1 ";
                if (!string.IsNullOrEmpty(filter)) sql += " And " + filter;
                DataSet ds = DbHelperSQL.Query(sql);
                //
                if (ds == null) return "";
                return JsonConvert.SerializeObject(ds.Tables[0]);
            }
            catch (Exception ex)
            {
                errorString = "";
                return "";
            }
        }
        public bool Exists(int Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Department");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
};
            parameters[0].Value = Id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 保存部门信息
        /// </summary>
        public int Add(Meeting.Model.Department model)
        {
            if (Exists(model.Id) == false) //新增
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into Department(");
                strSql.Append("ParentId,Sort,Name,Comment,IsActive)");
                strSql.Append(" values (");
                strSql.Append("@ParentId,@Sort,@Name,@Comment,@IsActive)");
                strSql.Append(";select @@IDENTITY");
                SqlParameter[] parameters = {
					new SqlParameter("@ParentId", SqlDbType.Int,4),
					new SqlParameter("@Sort", SqlDbType.Int,4),
					new SqlParameter("@Name", SqlDbType.NVarChar,50),
					new SqlParameter("@Comment", SqlDbType.NVarChar,200),
					new SqlParameter("@IsActive", SqlDbType.Bit,1)};
                parameters[0].Value = model.ParentId;
                parameters[1].Value = model.Sort;
                parameters[2].Value = model.Name;
                parameters[3].Value = model.Comment;
                parameters[4].Value = model.IsActive;
                try
                {
                    object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
                    if (obj == null)
                    {
                        return 0;
                    }
                    else
                    {
                        return Convert.ToInt32(obj);
                    }
                }
                catch (Exception ex) { errorString = ex.Message; return 0; }
            }
            else//修改
            {

                StringBuilder strSql = new StringBuilder();
                strSql.Append("update Department set ");
                strSql.Append("Sort=@Sort,");
                strSql.Append("Name=@Name,");
                strSql.Append("Comment=@Comment");
                strSql.Append(" where Id=@Id");
                SqlParameter[] parameters = { 
					new SqlParameter("@Sort", SqlDbType.Int,4),
					new SqlParameter("@Name", SqlDbType.NVarChar,50),
					new SqlParameter("@Comment", SqlDbType.NVarChar,200), 
					new SqlParameter("@Id", SqlDbType.Int,4)};
                parameters[0].Value = model.Sort;
                parameters[1].Value = model.Name;
                parameters[2].Value = model.Comment;
                parameters[3].Value = model.Id;
                try
                {
                    int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
                    if (rows > 0)
                    {
                        return model.Id;
                    }
                    else
                    {
                        return 0;
                    }
                }
                catch (Exception ex) { errorString = ex.Message; return 0; }
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Department ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
};
            parameters[0].Value = Id;
            try
            {
                int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
                if (rows > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return false;
            }

        }
    }
}
